﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    [Serializable]
    public sealed class ExpenseRateDtlsDAL : IExpenseRateDtlsDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public int Insert(ExpenseRateDtlsInfo expRateDtlsInfo)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@name", expRateDtlsInfo.Name);
                dbManager.AddParameters(1, "@descr", expRateDtlsInfo.Description);
                dbManager.AddParameters(2, "@exp", expRateDtlsInfo.ExpenseRate.Sequence);
                dbManager.AddParameters(3, "@rate", expRateDtlsInfo.Rate);
                dbManager.AddParameters(4, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@user", expRateDtlsInfo.User.CreatedBy);

                return dbManager.ExecuteNonQuery(CommandType.Text, "INSERT INTO BGM_MAS_EXPENSE_RATE_DTLS(BGM_EXP_RATE_DTLS_NAME, BGM_EXP_RATE_DTLS_DESCR, BGM_EXP_RATE_SEQ, RATE, RECORD_STATUS, CREATED_BY, UPDATED_BY)"
                                    + " VALUES (@name, @descr, @exp, @rate, @status, @user, @user)");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(ExpenseRateDtlsInfo expRateDtlsInfo)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@seq", expRateDtlsInfo.Sequence);
                dbManager.AddParameters(1, "@name", expRateDtlsInfo.Name);
                dbManager.AddParameters(2, "@descr", expRateDtlsInfo.Description);
                dbManager.AddParameters(3, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@exp", expRateDtlsInfo.ExpenseRate.Sequence);
                dbManager.AddParameters(5, "@rate", expRateDtlsInfo.Rate);
                dbManager.AddParameters(6, "@user", expRateDtlsInfo.User.CreatedBy);

                strSQL = "UPDATE BGM_MAS_EXPENSE_RATE_DTLS"
                + " SET BGM_EXP_RATE_DTLS_NAME = @name"
                    + ", BGM_EXP_RATE_DTLS_DESCR = @descr"
                    + ", RATE = @rate"
                    + ", RECORD_STATUS = @status"
                    + ", UPDATED_BY = @user"
                + " WHERE BGM_EXP_RATE_DTLS_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE BGM_MAS_EXPENSE_RATE_DTLS SET RECORD_STATUS = @status WHERE BGM_EXP_RATE_DTLS_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<ExpenseRateDtlsInfo> Search(ExpenseRateDtlsInfo expRateDtlsInfo)
        {
            List<ExpenseRateDtlsInfo> expRateDtls = new List<ExpenseRateDtlsInfo>();
            string strSQL;
            int exp = 0;

            if (expRateDtlsInfo.ExpenseRate != null)
                exp = expRateDtlsInfo.ExpenseRate.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@seq", expRateDtlsInfo.Sequence);
                dbManager.AddParameters(1, "@name", expRateDtlsInfo.Name);
                dbManager.AddParameters(2, "@descr", expRateDtlsInfo.Description);
                dbManager.AddParameters(3, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@exp", expRateDtlsInfo.ExpenseRate.Sequence);
                dbManager.AddParameters(5, "@rate", expRateDtlsInfo.Rate);
                dbManager.AddParameters(6, "@user", expRateDtlsInfo.User.CreatedBy);

                strSQL = "SELECT r.BGM_EXP_RATE_DTLS_SEQ, r.BGM_EXP_RATE_DTLS_NAME, r.BGM_EXP_RATE_DTLS_DESCR"
                    + ", r.RECORD_STATUS, r.RATE, rt.BGM_EXP_RATE_SEQ, r.UPDATED_BY"
                    + " FROM BGM_MAS_EXPENSE_RATE_DTLS r"
                        + " INNER JOIN BGM_MAS_EXPENSE_RATE rt ON r.BGM_EXP_RATE_SEQ = rt.BGM_EXP_RATE_SEQ"
                    + " WHERE r.RECORD_STATUS not like @status"
                        + ((expRateDtlsInfo.ExpenseRate.Sequence > 0) ? " AND rt.BGM_EXP_SEQ = @exp" : "")
                        + ((expRateDtlsInfo.Sequence > 0) ? " AND r.BGM_EXP_RATE_DTLS_SEQ = @seq" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ExpenseRateDtlsInfo expRateDtl = new ExpenseRateDtlsInfo();
                    expRateDtl.Sequence =  Convert.ToInt32(dr["BGM_EXP_RATE_DTLS_SEQ"]);
                    expRateDtl.Name = Convert.ToString(dr["BGM_EXP_RATE_DTLS_NAME"]);
                    expRateDtl.Description = Convert.ToString(dr["BGM_EXP_RATE_DTLS_DESCR"]);
                    expRateDtl.Status = Convert.ToString(dr["RECORD_STATUS"]);
                }

                //while (rdr.Read())
                //{
                //    ExpenseRateInfo expInfo = new ExpenseRateInfo();
                //    expInfo.Sequence = rdr.GetInt32(5);

                //    ExpenseRateDtlsInfo expRateDtlsRecord = new ExpenseRateDtlsInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetDouble(4), expInfo, new UserRecordInfo(1, rdr.GetInt32(6)));
                //    expRateDtls.Add(expRateDtlsRecord);
                //}

                return expRateDtls;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<ExpenseRateDtlsInfo> SearchExpRate(ExpenseRateDtlsInfo expRateDtlsInfo)
        {
            List<ExpenseRateDtlsInfo> expRateDtls = new List<ExpenseRateDtlsInfo>();
            string strSQL;
            int exp = 0;

            if (expRateDtlsInfo.ExpenseRate != null)
                exp = expRateDtlsInfo.ExpenseRate.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@seq", expRateDtlsInfo.Sequence);
                dbManager.AddParameters(1, "@name", expRateDtlsInfo.Name);
                dbManager.AddParameters(2, "@descr", expRateDtlsInfo.Description);
                dbManager.AddParameters(3, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@exp", expRateDtlsInfo.ExpenseRate.Sequence);
                dbManager.AddParameters(5, "@rate", expRateDtlsInfo.Rate);
                dbManager.AddParameters(6, "@user", expRateDtlsInfo.User.CreatedBy);

                strSQL = "SELECT r.BGM_EXP_RATE_DTLS_SEQ, r.BGM_EXP_RATE_DTLS_NAME, r.BGM_EXP_RATE_DTLS_DESCR"
                    + ", r.RECORD_STATUS, r.RATE, rt.BGM_EXP_RATE_SEQ, r.UPDATED_BY"
                    + " FROM BGM_MAS_EXPENSE_RATE_DTLS r"
                        + " INNER JOIN BGM_MAS_EXPENSE_RATE rt ON r.BGM_EXP_RATE_SEQ = rt.BGM_EXP_RATE_SEQ"
                    + " WHERE r.RECORD_STATUS not like @status"
                        + ((expRateDtlsInfo.ExpenseRate.Sequence > 0) ? " AND rt.BGM_EXP_RATE_SEQ = @exp" : "")
                        + ((expRateDtlsInfo.Sequence > 0) ? " AND r.BGM_EXP_RATE_DTLS_SEQ = @seq" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ExpenseRateDtlsInfo expRateDtl = new ExpenseRateDtlsInfo();
                    expRateDtl.Sequence = Convert.ToInt32(dr["BGM_EXP_RATE_DTLS_SEQ"]);
                    expRateDtl.Name = Convert.ToString(dr["BGM_EXP_RATE_DTLS_NAME"]);
                    expRateDtl.Description = Convert.ToString(dr["BGM_EXP_RATE_DTLS_DESCR"]);
                }

                //while (rdr.Read())
                //{
                //    ExpenseRateInfo expInfo = new ExpenseRateInfo();
                //    expInfo.Sequence = rdr.GetInt32(5);

                //    ExpenseRateDtlsInfo expRateDtlsRecord = new ExpenseRateDtlsInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetDouble(4), expInfo, new UserRecordInfo(1, rdr.GetInt32(6)));
                //    expRateDtls.Add(expRateDtlsRecord);
                //}

                return expRateDtls;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

    }
}
